<?php

namespace app\shop\logic;

use think\facade\Db;
use app\data\util\HttpUtil;
use ba\Exception;
use think\facade\Cache;
use app\common\library\Excel;
use app\shop\logic\DailyEstimateLogic;

class DashboardshopLogic
{
    protected $model = null;

    public function __construct()
    {

    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $shop_id = $params['shop_id'] ?? "";
        $date    = $params['date'] ?? '';
        $limit   = $params['limit'] ?? 20;
        $where   = [];
        if (!empty($shop_id)) {
            $where[] = ['a.shop_id', '=', $shop_id];
        }
        $thismonth = date('m');
        $thisyear  = date('Y');
        if (!$date) {
            $times = get_month_time($thisyear,$thismonth);
        }else{
            $date  = explode('-',$date);
            $times = get_month_time($date[0],$date[1]);
        }

        $start_time = $times['first'];
        $end_time   = $times['last'];

        $where[] = ['a.createtime', '>=', $start_time];
        $where[] = ['a.createtime', '<=', $end_time];

        if(!$shop_id){
            if(!$date){
                $cacheKey = 'MonthlyGrossProfitStatistics';
                $data = Cache::store('redis')->get($cacheKey);
                if(!$data){
                    $data = $this->MonthlyGrossProfitStatistics($start_time,$end_time,$limit,$where);
                    Cache::store('redis')->set($cacheKey,$data);
                }
            }else{
                if($date[1]>$thismonth){
                    $data = [];
                }elseif($date[1]==$thismonth){
                    $cacheKey = 'MonthlyGrossProfitStatistics';
                    $data = Cache::store('redis')->get($cacheKey);
                    if(!$data){
                        $data = $this->MonthlyGrossProfitStatistics($start_time,$end_time,$limit,$where);
                        Cache::store('redis')->set($cacheKey,$data);
                    }
                }else{
                    $cacheKey = 'MonthlyGrossProfitStatistics:'.$date[1];
                    $data = Cache::store('redis')->get($cacheKey);
                    if(!$data){
                        $data = $this->MonthlyGrossProfitStatistics($start_time,$end_time,$limit,$where);
                        Cache::store('redis')->set($cacheKey,$data);
                    }
                }

            }
        }else{
            $data = $this->MonthlyGrossProfitStatistics($start_time,$end_time,$limit,$where);
        }


        //毛利月度统计
       // $data = $this->MonthlyGrossProfitStatistics($start_time,$end_time,$limit,$where);
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function MonthlyGrossProfitStatistics($start_time,$end_time,$limit,$where)
    {

        
        $list = Db::name('order')
            ->field('
                COUNT(a.id) as count,
                a.shop_id,
                sum(total_amount) as total_amount,
                sum(shipping_fee_platform_discount) as shipping_fee_platform_discount,
                sum(platform_discount) as platform_discount,
                sum(original_total_product_price) as original_total_product_price,
                sum(tax) as tax,
                b.name as shop_name,
                sum(shipping_fee) as shipping_fee,
                SUM(c.nums) as nums
            ')
            ->alias('a')
            ->join('ba_shops b', 'b.id = a.shop_id', 'left')
            ->join("(SELECT order_id, SUM(num) as nums FROM ba_order_info GROUP BY order_id) c", 'c.order_id = a.order_id', 'left')
            ->where($where)
            ->where('a.status', '<>', 'CANCELLED')
            ->group('a.shop_id')
            ->order('total_amount desc')
            ->paginate($limit);

        $lists = $list->items();
        $lists = objectToArray($lists);
        //计算其他数据
        $exchange = Db::name('exchange')
            ->order('date desc')
            ->find();
        $sum['PanelSalesRevenue'] = 0;
        $sum['advertising_spend'] = 0;
        $sum['refund_subtotal'] = 0;
        if(!empty($lists)){
            //面板广告费
            $where_campaign = [];
            $where_campaign[] = ['a.stat_time', '>=', $start_time];
            $where_campaign[] = ['a.stat_time', '<=', $end_time];
            $where_campaign[] = ['a.spend', '>=', 0.001];
            //平台结算费
            $where_statement = [];
            $where_statement[] = ['a.statement_time', '>=', $start_time - 16*3600];
            $where_statement[] = ['a.statement_time', '<', $end_time - 16*3600];
            foreach ($lists as $k=>$v) {

                //平台结算费
                $referral_fee_amount = Db::name('order_statement')
                    ->field('
                        SUM(a.referral_fee_amount) as PlatformSettlementFee, 
                        ROUND(SUM(a.customer_refund_amount), 2) as customer_refund_amount, 
                    ')
                    ->alias('a')
                    ->join('ba_order d','a.order_id = d.order_id', 'left')
                    ->where($where_statement)
                    ->where('d.shop_id',$v['shop_id'])  
                    ->find();
                    
                if(!empty($referral_fee_amount['PlatformSettlementFee'])){
                    //平台结算费
                    $lists[$k]['PlatformSettlementFee'] = number_format($referral_fee_amount['PlatformSettlementFee'], 2, '.', '');
                }else{
                    $lists[$k]['PlatformSettlementFee'] = 0;
                }
                $lists[$k]['refund_subtotal'] = 0;
                if(!empty($referral_fee_amount['customer_refund_amount'])){
                    //退款
                    $lists[$k]['refund_subtotal'] = $referral_fee_amount['customer_refund_amount'];
                }

                //海外仓运费
                $lists[$k]['storage_fees'] = number_format((float) $v['count'] * $exchange['storage_fees'], 2, '.', '');
                //头程
                $lists[$k]['first_fees'] = number_format((float) $v['count'] * $exchange['first_fees'] / $exchange['proportion'], 2, '.', '');
                
                $DailyEstimateLogic = new DailyEstimateLogic();
                //采购成本
                $whereProcurementCost = [];
                $whereProcurementCost[] = ['b.createtime', '>=', $start_time];
                $whereProcurementCost[] = ['b.createtime', '<=', $end_time];
                $ProcurementCostTotalArr = $DailyEstimateLogic->ProcurementCost($whereProcurementCost,$v['shop_id'],$end_time);
                $lists[$k]['ProcurementCostTotal'] = number_format($ProcurementCostTotalArr['ProcurementCostTotal'] / $exchange['proportion'], 2, '.', '');

                //面板销售额
                $PanelSalesRevenue = number_format((float)$v['total_amount'] + $v['platform_discount'] + $v['shipping_fee_platform_discount'] - $v['tax'], 2, '.', '');
                $lists[$k]['PanelSalesRevenue'] = $PanelSalesRevenue;

                //面板广告费
                $advertising_spend = $DailyEstimateLogic->advertising_campaign($where_campaign,$v['shop_id']);

                $lists[$k]['advertising_spend'] = $advertising_spend;
                
                //ROI
                if($lists[$k]['PanelSalesRevenue'] > 0.001 && $lists[$k]['advertising_spend'] > 0.001){
                    $lists[$k]['roi'] = number_format((float)$lists[$k]['PanelSalesRevenue'] / $lists[$k]['advertising_spend'], 2, '.', '');
                }else{
                    $lists[$k]['roi'] = 0;
                }
                //实际盈利
                $lists[$k]['ActualProfit'] = number_format((float)$lists[$k]['PanelSalesRevenue'] - $lists[$k]['advertising_spend'] - $lists[$k]['storage_fees'] - $lists[$k]['first_fees'] - $lists[$k]['ProcurementCostTotal'], 2, '.', '');

                //总和
                $sum['PanelSalesRevenue'] += $PanelSalesRevenue;
                $sum['advertising_spend'] += $advertising_spend;
                $sum['refund_subtotal'] += $lists[$k]['refund_subtotal'];
            }
        }

        $data = [
            'list'  => $lists,
            'total' => $list->total(),
            'PanelSalesRevenue' => number_format((float)$sum['PanelSalesRevenue'], 2, '.', ''),
            'advertising_spend' => number_format((float)$sum['advertising_spend'], 2, '.', ''),
            'refund_subtotal'   => number_format((float)$sum['refund_subtotal'], 2, '.', ''),
        ];
        return $data;
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $lists = [];
        $header[] = ['广告账户', 'advertiser_id'];
        $header[] = ['广告名称', 'advertiser_name'];
        $header[] = ['花费', 'spend'];
        $header[] = ['统计月份', 'month'];
        $fileName = "export_Dashboardshop" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }
    
}
